package com.wxtf.platform.main.proprietaryInquires;

import javax.servlet.http.HttpServletRequest;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.wxtf.platform.interfaces.CommonsCallProvider;
import com.wxtf.platform.plugins.user.UserBean;
import com.wxtf.platform.supers.LogicBase;
import com.wxtf.platform.tools.ConstantInfoHelper;
import com.wxtf.platform.tools.AccessDataBaseUtil;
import com.wxtf.platform.tools.FormatUtil;
import com.wxtf.platform.tools.XLSUtils;
import java.io.File;
import java.util.ArrayList;
import java.util.List;

import com.wxtf.platform.tools.Common;
import com.wxtf.platform.main.proprietaryInquires.Married;
/**
 * <li>Title: Pregnant.java</li>
 * <li>Project: baseplatform</li>
 * <li>Package: com.wxtf.platform.main.proprietaryInquires</li>
 * <li>Description: 育龄孕妇</li>
 * <li>Copyright: Copyright (c) 2012</li>
 * <li>Company: wxthtf Technologies </li>
 * <li>Created on Sep 21, 2012 10:29:02 AM</li>
 * 
 * @author wangzhi
 * @version 1.0.0.0
 * 
 */
public class Pregnant extends LogicBase implements CommonsCallProvider{
	private static final Log log = LogFactory.getLog(Pregnant.class);

	public Pregnant(String jsonArray){

	}
	
	@Override
	public Object run() {
		Object msg = "";
		try {
			if("queryPage".equals(this.getMethodName())) {
				msg = this.select(this.getLogicData());
				return msg;
			} else if("new_add".equals(this.getMethodName())) {
				msg = this.new_add(this.getLogicData());
				return msg;
			} else if("huizong".equals(this.getMethodName())) {
				msg = this.huizong(this.getLogicData());
				return msg;
			} else if("download".equals(this.getMethodName())) {
				msg = this.download(this.getLogicData());
			} else if("huaiyun_dengyun".equals(this.getMethodName())) {
				msg = this.huaiyun_dengyun(this.getLogicData());
				return msg;
			} 
			else {
				msg = "\"没有【" + this.getMethodName() + "】该方法\"";
			}

			if(msg == null || "[null]".equals(msg)) {
				msg = "\"\"";
			}
			if(msg.toString().startsWith("{") || msg.toString().startsWith("[")){
				return msg;
			}
			msg = ConstantInfoHelper.generateJSONString(msg.toString());
		} catch(Exception e) {
			msg = ConstantInfoHelper.generateErrorJSONString();
			log.error(e, e);
		} finally {
			this.clear();
		}
		return msg.toString();
	}

	String[] fields = {
			"reserve1","tbe_id","org_id","tbe_name","tbe_idcard","tbe_sex","tbe_birthday","tbe_nation","tbe_position","tbe_marital_status","tbe_children_status","tbepr_option","tbepr_ptime","chizheng"
	};
	@Override
	public Object select(Object jsonArray) throws Exception {
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String type= jsonObject.getString("type");
		String sql=" from tb_employee t1 left join tb_family_base t2 on t1.tbe_id=t2.tbe_id left join tbe_pregnancy_record t3 on t3.tbfi_id=t2.tbfi_id where t1.tbe_sex='女' and t1.tbe_status not in ('102-3','102-4','102-5') and t1.tbe_employment_forms <> '31' and t3.tbepr_option in ('0','1') and "+ Married.from20to49("t1.tbe_birthday")+" ";
		if("init".equals(type)) {
				sql = sql + " and t1.org_id in (" + user.getCurSubOrgId() + ") ";
		} else if("tj_ser".equals(type)) {
			String ser_from= jsonObject.getString("ser_from");
			String ser_to = jsonObject.getString("ser_to");
			String orgIds = jsonObject.getString("ser_org");
			String ser_pre_situ = jsonObject.getString("ser_pre_situ");
			if(StringUtils.isBlank(orgIds)){
				sql=sql+" and t1.org_id in ("+user.getCurSubOrgId()+") ";
			} else {
				sql=sql+" and t1.org_id in ("+user.getCurSubOrgIds(orgIds)+") ";
			}
			if(StringUtils.isNotBlank(ser_from)){
				sql=sql+" and t1.tbe_birthday >= '"+ser_from+"' ";
			}
			if(StringUtils.isNotBlank(ser_to)){
				sql=sql+" and t1.tbe_birthday <= '"+ser_to+"' ";
			}
			if(StringUtils.isNotBlank(ser_pre_situ)){
				sql=sql+" and t3.tbepr_option = '"+ser_pre_situ+"' ";
			}
		}
		String sortname = this.getRequest().getParameter("sortname");
		String sortorder = this.getRequest().getParameter("sortorder");
		if(sortname!=null&&sortname!=null) {
			sql += " order by " + sortname + " " + sortorder;
		}
		String countSql = "select count(*) " + sql;
		String querySql = "select t1.reserve1,t1.tbe_id,t1.org_id,t1.tbe_name,t1.tbe_idcard,t1.tbe_sex,t1.tbe_birthday,t1.tbe_nation,t1.tbe_position,t1.tbe_marital_status,t1.tbe_children_status,t3.tbepr_option,t3.tbepr_ptime,(select a1.tbefbr_type from tbe_faa_birth_record a1 where (a1.tbfi_id=t2.tbfi_id or a1.tbfi_id in (select tbfi_id from tb_family_base where tbe_id=t2.tbe_spouse_id)) order by a1.create_time desc limit 1 ) as chizheng "+sql;
		return AccessDataBaseUtil.query4SQLPagging(fields, countSql, querySql, this.getPage(), this.getPagesize(), null);
	}
     
	//本月新增孕妇名单
	public Object new_add(Object jsonArray) throws Exception {
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String type= jsonObject.getString("type");
		
		String curTime = FormatUtil.getCurentDate();//获取当前时间
		String []time_now=curTime.split("-");
		
		String sql=" from tb_employee t1 left join tb_family_base t2 on t1.tbe_id=t2.tbe_id left join tbe_pregnancy_record t3 on t3.tbfi_id=t2.tbfi_id where t1.tbe_sex='女' and t1.tbe_status not in ('102-3','102-4','102-5') and t1.tbe_employment_forms <> '31' and t3.tbepr_option in ('0','1') and "+Married.from20to49("t1.tbe_birthday")+" and month(t3.tbepr_ptime)="+time_now[1]+" and year(t3.tbepr_ptime)="+time_now[0];

		if("init".equals(type)) {
			sql = sql + " and t1.org_id in (" + user.getCurSubOrgId() + ") ";
		} else if("tj_ser".equals(type)) {
			String ser_from= jsonObject.getString("ser_from");
			String ser_to = jsonObject.getString("ser_to");
			String orgIds = jsonObject.getString("ser_org");
			if(StringUtils.isBlank(orgIds)){
				sql=sql+" and t1.org_id in ("+user.getCurSubOrgId()+") ";
			} else {
				sql=sql+" and t1.org_id in ("+user.getCurSubOrgIds(orgIds)+") ";
			}
			if(StringUtils.isNotBlank(ser_from)){
				sql=sql+" and t1.tbe_birthday >= '"+ser_from+"' ";
			}
			if(StringUtils.isNotBlank(ser_to)){
				sql=sql+" and t1.tbe_birthday <= '"+ser_to+"' ";
			}	
		}
		String sortname = this.getRequest().getParameter("sortname");
		String sortorder = this.getRequest().getParameter("sortorder");
		if(sortname!=null&&sortname!=null) {
			sql += " order by " + sortname + " " + sortorder;
		}
		String countSql = "select count(*) " + sql;
		String querySql = "select t1.reserve1,t1.tbe_id,t1.org_id,t1.tbe_name,t1.tbe_idcard,t1.tbe_sex,t1.tbe_birthday,t1.tbe_nation,t1.tbe_position,t1.tbe_marital_status,t1.tbe_children_status,t3.tbepr_option,t3.tbepr_ptime,(select a1.tbefbr_type from tbe_faa_birth_record a1 where (a1.tbfi_id=t2.tbfi_id or a1.tbfi_id in (select tbfi_id from tb_family_base where tbe_id=t2.tbe_spouse_id)) order by a1.create_time desc limit 1 ) as chizheng  "+sql;
		return AccessDataBaseUtil.query4SQLPagging(fields, countSql, querySql, this.getPage(), this.getPagesize(), null);	
	}
	
	
	
	
	@Override
	public Object insert(Object jsonArray) throws Exception {
		return null;
	}

	@Override
	public Object update(Object jsonArray) throws Exception {
		return null;
	}
	
	@Override
	public Object delete(Object jsonArray) throws Exception {
		return null;
	}

	@Override
	public Object recordCount(Object jsonArray) throws Exception {
		return null;
	}

	@Override
	public Object setRequest(HttpServletRequest request) throws Exception {
		this.request = request;
		return request;
	}

	@Override
	public Object updateBatch(Object jsonArray) throws Exception {
		return null;
	}

	@Override
	public Object onCreate(Object jsonArray) {
		return null;
	}

	@Override
	public Object onDestory(Object jsonArray) {
		return null;
	}

	@Override
	public Object onInit(Object jsonArray) {
		return null;
	}
	
	public Object huizong (Object jsonArray) throws Exception {
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		StringBuffer sb = new StringBuffer();
		String lll=user.getCurSubOrgId();
		String[] org=lll.split(",");
		
		//*********************************
		String sql_num="select count(*) from t_department where levels>=2 and department_id in ("+lll+")";
		int row_num=AccessDataBaseUtil.queryForInt(sql_num, null); //得到非0,1级机构的条数
		String [] org_fin=new String[row_num];//最后得到的机构数组
		String sql="";
		int jishu=0;
		for(int i=0;i<org.length;i++)
		{
			sql="select levels from t_department where department_id="+org[i];
			int lev=Integer.valueOf( AccessDataBaseUtil.query4List(sql, null).toString().replace("[", "").replace("]", "").replace(" ", "")).intValue();
			if(lev>=2)
			{
				org_fin[jishu]=org[i];
				jishu++;
			}
		}
		//*********************************
		
		sb.append("{\"Total\":\""+row_num+"\",\"OrgName\":\""+user.getCurOrgName()+"\",\"Rows\":[");
		for(int i = 0; i < org_fin.length; i++) {
			if(i!=0)
			{
				sb.append(",");
			}
			String sql_1="select count(*) from tb_employee t1 left join tb_family_base t2 on t1.tbe_id=t2.tbe_id left join tbe_pregnancy_record t3 on t3.tbfi_id=t2.tbfi_id where t1.tbe_sex='女' and t1.tbe_status not in ('102-3','102-4','102-5') and t1.tbe_employment_forms <> '31' and t3.tbepr_option in ('0','1') and  "+Married.from20to49("t1.tbe_birthday")+" and month(t3.tbepr_ptime)='01' and year(t3.tbepr_ptime)=year(CURRENT_DATE) and t1.org_id="+org_fin[i]+" ";
			String sql_2="select count(*) from tb_employee t1 left join tb_family_base t2 on t1.tbe_id=t2.tbe_id left join tbe_pregnancy_record t3 on t3.tbfi_id=t2.tbfi_id where t1.tbe_sex='女' and t1.tbe_status not in ('102-3','102-4','102-5') and t1.tbe_employment_forms <> '31' and t3.tbepr_option in ('0','1') and  "+Married.from20to49("t1.tbe_birthday")+" and month(t3.tbepr_ptime)='02' and year(t3.tbepr_ptime)=year(CURRENT_DATE) and t1.org_id="+org_fin[i]+" ";
			String sql_3="select count(*) from tb_employee t1 left join tb_family_base t2 on t1.tbe_id=t2.tbe_id left join tbe_pregnancy_record t3 on t3.tbfi_id=t2.tbfi_id where t1.tbe_sex='女' and t1.tbe_status not in ('102-3','102-4','102-5') and t1.tbe_employment_forms <> '31' and t3.tbepr_option in ('0','1') and  "+Married.from20to49("t1.tbe_birthday")+" and month(t3.tbepr_ptime)='03' and year(t3.tbepr_ptime)=year(CURRENT_DATE) and t1.org_id="+org_fin[i]+" ";
			String sql_4="select count(*) from tb_employee t1 left join tb_family_base t2 on t1.tbe_id=t2.tbe_id left join tbe_pregnancy_record t3 on t3.tbfi_id=t2.tbfi_id where t1.tbe_sex='女' and t1.tbe_status not in ('102-3','102-4','102-5') and t1.tbe_employment_forms <> '31' and t3.tbepr_option in ('0','1') and  "+Married.from20to49("t1.tbe_birthday")+" and month(t3.tbepr_ptime)='04' and year(t3.tbepr_ptime)=year(CURRENT_DATE) and t1.org_id="+org_fin[i]+" ";
			String sql_5="select count(*) from tb_employee t1 left join tb_family_base t2 on t1.tbe_id=t2.tbe_id left join tbe_pregnancy_record t3 on t3.tbfi_id=t2.tbfi_id where t1.tbe_sex='女' and t1.tbe_status not in ('102-3','102-4','102-5') and t1.tbe_employment_forms <> '31' and t3.tbepr_option in ('0','1') and  "+Married.from20to49("t1.tbe_birthday")+" and month(t3.tbepr_ptime)='05' and year(t3.tbepr_ptime)=year(CURRENT_DATE) and t1.org_id="+org_fin[i]+" ";
			String sql_6="select count(*) from tb_employee t1 left join tb_family_base t2 on t1.tbe_id=t2.tbe_id left join tbe_pregnancy_record t3 on t3.tbfi_id=t2.tbfi_id where t1.tbe_sex='女' and t1.tbe_status not in ('102-3','102-4','102-5') and t1.tbe_employment_forms <> '31' and t3.tbepr_option in ('0','1') and  "+Married.from20to49("t1.tbe_birthday")+" and month(t3.tbepr_ptime)='06' and year(t3.tbepr_ptime)=year(CURRENT_DATE) and t1.org_id="+org_fin[i]+" ";
			String sql_7="select count(*) from tb_employee t1 left join tb_family_base t2 on t1.tbe_id=t2.tbe_id left join tbe_pregnancy_record t3 on t3.tbfi_id=t2.tbfi_id where t1.tbe_sex='女' and t1.tbe_status not in ('102-3','102-4','102-5') and t1.tbe_employment_forms <> '31' and t3.tbepr_option in ('0','1') and  "+Married.from20to49("t1.tbe_birthday")+" and month(t3.tbepr_ptime)='07' and year(t3.tbepr_ptime)=year(CURRENT_DATE) and t1.org_id="+org_fin[i]+" ";
			String sql_8="select count(*) from tb_employee t1 left join tb_family_base t2 on t1.tbe_id=t2.tbe_id left join tbe_pregnancy_record t3 on t3.tbfi_id=t2.tbfi_id where t1.tbe_sex='女' and t1.tbe_status not in ('102-3','102-4','102-5') and t1.tbe_employment_forms <> '31' and t3.tbepr_option in ('0','1') and  "+Married.from20to49("t1.tbe_birthday")+" and month(t3.tbepr_ptime)='08' and year(t3.tbepr_ptime)=year(CURRENT_DATE) and t1.org_id="+org_fin[i]+" ";
			String sql_9="select count(*) from tb_employee t1 left join tb_family_base t2 on t1.tbe_id=t2.tbe_id left join tbe_pregnancy_record t3 on t3.tbfi_id=t2.tbfi_id where t1.tbe_sex='女' and t1.tbe_status not in ('102-3','102-4','102-5') and t1.tbe_employment_forms <> '31' and t3.tbepr_option in ('0','1') and  "+Married.from20to49("t1.tbe_birthday")+" and month(t3.tbepr_ptime)='09' and year(t3.tbepr_ptime)=year(CURRENT_DATE) and t1.org_id="+org_fin[i]+" ";
			String sql_10="select count(*) from tb_employee t1 left join tb_family_base t2 on t1.tbe_id=t2.tbe_id left join tbe_pregnancy_record t3 on t3.tbfi_id=t2.tbfi_id where t1.tbe_sex='女' and t1.tbe_status not in ('102-3','102-4','102-5') and t1.tbe_employment_forms <> '31' and t3.tbepr_option in ('0','1') and  "+Married.from20to49("t1.tbe_birthday")+" and month(t3.tbepr_ptime)='10' and year(t3.tbepr_ptime)=year(CURRENT_DATE) and t1.org_id="+org_fin[i]+" ";
			String sql_11="select count(*) from tb_employee t1 left join tb_family_base t2 on t1.tbe_id=t2.tbe_id left join tbe_pregnancy_record t3 on t3.tbfi_id=t2.tbfi_id where t1.tbe_sex='女' and t1.tbe_status not in ('102-3','102-4','102-5') and t1.tbe_employment_forms <> '31' and t3.tbepr_option in ('0','1') and  "+Married.from20to49("t1.tbe_birthday")+" and month(t3.tbepr_ptime)='11' and year(t3.tbepr_ptime)=year(CURRENT_DATE) and t1.org_id="+org_fin[i]+" ";
			String sql_12="select count(*) from tb_employee t1 left join tb_family_base t2 on t1.tbe_id=t2.tbe_id left join tbe_pregnancy_record t3 on t3.tbfi_id=t2.tbfi_id where t1.tbe_sex='女' and t1.tbe_status not in ('102-3','102-4','102-5') and t1.tbe_employment_forms <> '31' and t3.tbepr_option in ('0','1') and  "+Married.from20to49("t1.tbe_birthday")+" and month(t3.tbepr_ptime)='12' and year(t3.tbepr_ptime)=year(CURRENT_DATE) and t1.org_id="+org_fin[i]+" ";
			String sql_sum="select count(*) from tb_employee t1 left join tb_family_base t2 on t1.tbe_id=t2.tbe_id left join tbe_pregnancy_record t3 on t3.tbfi_id=t2.tbfi_id where t1.tbe_sex='女' and t1.tbe_status not in ('102-3','102-4','102-5') and t1.tbe_employment_forms <> '31' and t3.tbepr_option in ('0','1') and "+Married.from20to49("t1.tbe_birthday")+"  and year(t3.tbepr_ptime)=year(CURRENT_DATE) and t1.org_id="+org_fin[i]+" ";
			sb.append("{\"name1\":\""+find_orgName(org_fin[i])+"\", \"name2\":\""+AccessDataBaseUtil.queryForInt(sql_1, null)+"\", \"name3\": \""+AccessDataBaseUtil.queryForInt(sql_2, null)+"\", \"name4\": \""+AccessDataBaseUtil.queryForInt(sql_3, null)+"\", \"name5\":\""+AccessDataBaseUtil.queryForInt(sql_4, null)+"\", \"name6\": \""+AccessDataBaseUtil.queryForInt(sql_5, null)+"\", \"name7\":\""+AccessDataBaseUtil.queryForInt(sql_6, null)+"\", \"name8\":\""+AccessDataBaseUtil.queryForInt(sql_7, null)+"\", \"name9\":\""+AccessDataBaseUtil.queryForInt(sql_8, null)+"\", \"name10\":\""+AccessDataBaseUtil.queryForInt(sql_9, null)+"\", \"name11\":\""+AccessDataBaseUtil.queryForInt(sql_10, null)+"\", \"name12\":\""+AccessDataBaseUtil.queryForInt(sql_11, null)+"\", \"name13\":\""+AccessDataBaseUtil.queryForInt(sql_12, null)+"\",\"name14\":\""+AccessDataBaseUtil.queryForInt(sql_sum, null)+"\"}");
		}
		String sql_heji="select count(*) from tb_employee t1 left join tb_family_base t2 on t1.tbe_id=t2.tbe_id left join tbe_pregnancy_record t3 on t3.tbfi_id=t2.tbfi_id where t1.tbe_sex='女' and t1.tbe_status not in ('102-3','102-4','102-5') and t1.tbe_employment_forms <> '31' and t3.tbepr_option in ('0','1') and  "+Married.from20to49("t1.tbe_birthday")+"  and year(t3.tbepr_ptime)=year(CURRENT_DATE) and t1.org_id in ("+user.getCurSubOrgId()+") ";
		sb.append("],\"Heji\":\"").append(AccessDataBaseUtil.queryForInt(sql_heji, null)).append("\"}");	
		//System.out.println(sb.toString());
		return sb.toString();
	}
	
	public String find_orgName(String org_id) throws Exception {
		String orgId=org_id;
		String sql="select caption from t_department where department_id="+orgId+" ";
		String val_org_name= AccessDataBaseUtil.query4List(sql, null).toString().replace("[", "").replace("]", "");
		return val_org_name;
	}
	
	/**
	 * 
	 * 描述：下载报表
	 * @param jsonArray
	 * @return Object
	 * @throws Exception
	 * @CreateOn Sep 27, 2012 16:03:36 PM
	 * @author wangzhi
	 */
	public Object download(String jsonArray) throws Exception {
		List<Object[]> list = new ArrayList<Object[]>();
		
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		String lll=user.getCurSubOrgId();
		String[] org=lll.split(",");
		//*********************************
		String sql_num="select count(*) from t_department where levels>=2 and department_id in ("+lll+")";
		int row_num=AccessDataBaseUtil.queryForInt(sql_num, null); //得到非0,1级机构的条数
		String [] org_fin=new String[row_num];//最后得到的机构数组
		String sql="";
		int jishu=0;
		for(int i=0;i<org.length;i++)
		{
			sql="select levels from t_department where department_id="+org[i];
			int lev=Integer.valueOf( AccessDataBaseUtil.query4List(sql, null).toString().replace("[", "").replace("]", "").replace(" ", "")).intValue();
			if(lev>=2)
			{
				org_fin[jishu]=org[i];
				jishu++;
			}
		}
		//*********************************
		for(int i = 0; i < org_fin.length; i++) {
			String sql_1="select count(*) from tb_employee t1 left join tb_family_base t2 on t1.tbe_id=t2.tbe_id left join tbe_pregnancy_record t3 on t3.tbfi_id=t2.tbfi_id where t1.tbe_sex='女' and t1.tbe_status not in ('102-3','102-4','102-5') and t1.tbe_employment_forms <> '31' and t3.tbepr_option in ('0','1') and  "+Married.from20to49("t1.tbe_birthday")+" and month(t3.tbepr_ptime)='01' and year(t3.tbepr_ptime)=year(CURRENT_DATE) and t1.org_id="+org_fin[i]+" ";
			String sql_2="select count(*) from tb_employee t1 left join tb_family_base t2 on t1.tbe_id=t2.tbe_id left join tbe_pregnancy_record t3 on t3.tbfi_id=t2.tbfi_id where t1.tbe_sex='女' and t1.tbe_status not in ('102-3','102-4','102-5') and t1.tbe_employment_forms <> '31' and t3.tbepr_option in ('0','1') and  "+Married.from20to49("t1.tbe_birthday")+" and month(t3.tbepr_ptime)='02' and year(t3.tbepr_ptime)=year(CURRENT_DATE) and t1.org_id="+org_fin[i]+" ";
			String sql_3="select count(*) from tb_employee t1 left join tb_family_base t2 on t1.tbe_id=t2.tbe_id left join tbe_pregnancy_record t3 on t3.tbfi_id=t2.tbfi_id where t1.tbe_sex='女' and t1.tbe_status not in ('102-3','102-4','102-5') and t1.tbe_employment_forms <> '31' and t3.tbepr_option in ('0','1') and  "+Married.from20to49("t1.tbe_birthday")+" and month(t3.tbepr_ptime)='03' and year(t3.tbepr_ptime)=year(CURRENT_DATE) and t1.org_id="+org_fin[i]+" ";
			String sql_4="select count(*) from tb_employee t1 left join tb_family_base t2 on t1.tbe_id=t2.tbe_id left join tbe_pregnancy_record t3 on t3.tbfi_id=t2.tbfi_id where t1.tbe_sex='女' and t1.tbe_status not in ('102-3','102-4','102-5') and t1.tbe_employment_forms <> '31' and t3.tbepr_option in ('0','1') and  "+Married.from20to49("t1.tbe_birthday")+" and month(t3.tbepr_ptime)='04' and year(t3.tbepr_ptime)=year(CURRENT_DATE) and t1.org_id="+org_fin[i]+" ";
			String sql_5="select count(*) from tb_employee t1 left join tb_family_base t2 on t1.tbe_id=t2.tbe_id left join tbe_pregnancy_record t3 on t3.tbfi_id=t2.tbfi_id where t1.tbe_sex='女' and t1.tbe_status not in ('102-3','102-4','102-5') and t1.tbe_employment_forms <> '31' and t3.tbepr_option in ('0','1') and  "+Married.from20to49("t1.tbe_birthday")+" and month(t3.tbepr_ptime)='05' and year(t3.tbepr_ptime)=year(CURRENT_DATE) and t1.org_id="+org_fin[i]+" ";
			String sql_6="select count(*) from tb_employee t1 left join tb_family_base t2 on t1.tbe_id=t2.tbe_id left join tbe_pregnancy_record t3 on t3.tbfi_id=t2.tbfi_id where t1.tbe_sex='女' and t1.tbe_status not in ('102-3','102-4','102-5') and t1.tbe_employment_forms <> '31' and t3.tbepr_option in ('0','1') and  "+Married.from20to49("t1.tbe_birthday")+" and month(t3.tbepr_ptime)='06' and year(t3.tbepr_ptime)=year(CURRENT_DATE) and t1.org_id="+org_fin[i]+" ";
			String sql_7="select count(*) from tb_employee t1 left join tb_family_base t2 on t1.tbe_id=t2.tbe_id left join tbe_pregnancy_record t3 on t3.tbfi_id=t2.tbfi_id where t1.tbe_sex='女' and t1.tbe_status not in ('102-3','102-4','102-5') and t1.tbe_employment_forms <> '31' and t3.tbepr_option in ('0','1') and  "+Married.from20to49("t1.tbe_birthday")+" and month(t3.tbepr_ptime)='07' and year(t3.tbepr_ptime)=year(CURRENT_DATE) and t1.org_id="+org_fin[i]+" ";
			String sql_8="select count(*) from tb_employee t1 left join tb_family_base t2 on t1.tbe_id=t2.tbe_id left join tbe_pregnancy_record t3 on t3.tbfi_id=t2.tbfi_id where t1.tbe_sex='女' and t1.tbe_status not in ('102-3','102-4','102-5') and t1.tbe_employment_forms <> '31' and t3.tbepr_option in ('0','1') and  "+Married.from20to49("t1.tbe_birthday")+" and month(t3.tbepr_ptime)='08' and year(t3.tbepr_ptime)=year(CURRENT_DATE) and t1.org_id="+org_fin[i]+" ";
			String sql_9="select count(*) from tb_employee t1 left join tb_family_base t2 on t1.tbe_id=t2.tbe_id left join tbe_pregnancy_record t3 on t3.tbfi_id=t2.tbfi_id where t1.tbe_sex='女' and t1.tbe_status not in ('102-3','102-4','102-5') and t1.tbe_employment_forms <> '31' and t3.tbepr_option in ('0','1') and  "+Married.from20to49("t1.tbe_birthday")+" and month(t3.tbepr_ptime)='09' and year(t3.tbepr_ptime)=year(CURRENT_DATE) and t1.org_id="+org_fin[i]+" ";
			String sql_10="select count(*) from tb_employee t1 left join tb_family_base t2 on t1.tbe_id=t2.tbe_id left join tbe_pregnancy_record t3 on t3.tbfi_id=t2.tbfi_id where t1.tbe_sex='女' and t1.tbe_status not in ('102-3','102-4','102-5') and t1.tbe_employment_forms <> '31' and t3.tbepr_option in ('0','1') and  "+Married.from20to49("t1.tbe_birthday")+" and month(t3.tbepr_ptime)='10' and year(t3.tbepr_ptime)=year(CURRENT_DATE) and t1.org_id="+org_fin[i]+" ";
			String sql_11="select count(*) from tb_employee t1 left join tb_family_base t2 on t1.tbe_id=t2.tbe_id left join tbe_pregnancy_record t3 on t3.tbfi_id=t2.tbfi_id where t1.tbe_sex='女' and t1.tbe_status not in ('102-3','102-4','102-5') and t1.tbe_employment_forms <> '31' and t3.tbepr_option in ('0','1') and  "+Married.from20to49("t1.tbe_birthday")+" and month(t3.tbepr_ptime)='11' and year(t3.tbepr_ptime)=year(CURRENT_DATE) and t1.org_id="+org_fin[i]+" ";
			String sql_12="select count(*) from tb_employee t1 left join tb_family_base t2 on t1.tbe_id=t2.tbe_id left join tbe_pregnancy_record t3 on t3.tbfi_id=t2.tbfi_id where t1.tbe_sex='女' and t1.tbe_status not in ('102-3','102-4','102-5') and t1.tbe_employment_forms <> '31' and t3.tbepr_option in ('0','1') and  "+Married.from20to49("t1.tbe_birthday")+" and month(t3.tbepr_ptime)='12' and year(t3.tbepr_ptime)=year(CURRENT_DATE) and t1.org_id="+org_fin[i]+" ";
			String sql_sum="select count(*) from tb_employee t1 left join tb_family_base t2 on t1.tbe_id=t2.tbe_id left join tbe_pregnancy_record t3 on t3.tbfi_id=t2.tbfi_id where t1.tbe_sex='女' and t1.tbe_status not in ('102-3','102-4','102-5') and t1.tbe_employment_forms <> '31' and t3.tbepr_option in ('0','1') and "+Married.from20to49("t1.tbe_birthday")+"  and year(t3.tbepr_ptime)=year(CURRENT_DATE) and t1.org_id="+org_fin[i]+" ";
			String[] arr ={find_orgName(org_fin[i]),String.valueOf(AccessDataBaseUtil.queryForInt(sql_1, null)),String.valueOf(AccessDataBaseUtil.queryForInt(sql_2, null)),String.valueOf(AccessDataBaseUtil.queryForInt(sql_3, null)),String.valueOf(AccessDataBaseUtil.queryForInt(sql_4, null)),String.valueOf(AccessDataBaseUtil.queryForInt(sql_5, null)),String.valueOf(AccessDataBaseUtil.queryForInt(sql_6, null)),String.valueOf(AccessDataBaseUtil.queryForInt(sql_7, null)),String.valueOf(AccessDataBaseUtil.queryForInt(sql_8, null)),String.valueOf(AccessDataBaseUtil.queryForInt(sql_9, null)),String.valueOf(AccessDataBaseUtil.queryForInt(sql_10, null)),String.valueOf(AccessDataBaseUtil.queryForInt(sql_11, null)),String.valueOf(AccessDataBaseUtil.queryForInt(sql_12, null)),String.valueOf(AccessDataBaseUtil.queryForInt(sql_sum, null))};
			list.add(arr);
		}
		String url = this.getRequest().getSession().getServletContext().getRealPath("/jsp/app/xls/");
		String model = url + "/model/pregnant_newadd.xls";
		String file = url + "/file/pregnant_newadd.xls";
		String rfile = "jsp/app/xls/file/pregnant_newadd.xls";
		File ffrom = new File(model);
		File fto = new File(file);
		Common.copyFile(ffrom, fto);

		if(list.size() > 0){
			XLSUtils.writeXls(list, fto, "新增孕妇人数汇总", 4);//写xls内容
		} else {
			return "{\"result\":false,\"resultmessage\":\"调用失败\",\"resultrecord\":\"导出结果集没有记录\"}";
		}
		return "\"" + rfile + "\"";
	}
	
	public Object huaiyun_dengyun(String jsonArray) throws Exception {
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		int row_huaiyun=0;
		int row_dengyun=0;
		int row_zong=0;
		String sql=" from tb_employee t1 left join tb_family_base t2 on t1.tbe_id=t2.tbe_id left join tbe_pregnancy_record t3 on t3.tbfi_id=t2.tbfi_id where t1.tbe_sex='女' and t1.tbe_status not in ('102-3','102-4','102-5') and t1.tbe_employment_forms <> '31' and t3.tbepr_option in ('0','1') and "+Married.from20to49("t1.tbe_birthday")+"  and t1.org_id in (" + user.getCurSubOrgId() + ") ";
		String sql_huaiyun="select count(*) "+sql+" and t3.tbepr_option='0' ";
		String sql_dengyun="select count(*) "+sql+" and t3.tbepr_option='1' ";
		String sql_zong="select count(*) "+sql;
		row_huaiyun=AccessDataBaseUtil.queryForInt(sql_huaiyun, null);
		row_dengyun=AccessDataBaseUtil.queryForInt(sql_dengyun, null);
		row_zong=AccessDataBaseUtil.queryForInt(sql_zong, null);
		String json_yun="{\"Total\":\"\",\"Rows\":[],\"huaiyun\":\""+row_huaiyun+"\",\"dengyun\":\""+row_dengyun+"\",\"zong\":\""+row_zong+"\"}";
		return json_yun;
	}
	
	public int cur_age(String tbe_birthday,String cur_day)
	{
		String[] bir=tbe_birthday.split("-");
		String[] curday=cur_day.split("-");
		if(Integer.parseInt(curday[0])-Integer.parseInt(bir[0])>=0)
		{
			int val_age=Integer.parseInt(curday[0])-Integer.parseInt(bir[0]);
			if(Integer.parseInt(curday[1])-Integer.parseInt(bir[1])<0 ) //比较月份【当前月<出生月】
			{
				return val_age-1;
			}
			else if(Integer.parseInt(curday[1])-Integer.parseInt(bir[1])>=0)//比较月份【当前月>=出生月】
			{
				if(Integer.parseInt(curday[2])-Integer.parseInt(bir[2])<0)//比较日【当前日<出生日】
				{
					return val_age-1;
				}
				return val_age;
			}
			return val_age;
	  }
	  return 0;
	}
}


